﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using Ylh.DBUtility;

namespace UpdateDatabase
{
    public class UpdateDatabase201210
    {
        /// <summary>
        /// 用来获取脚本执行情况
        /// </summary>
        public List<string> list = new List<string>();
        /// <summary>
        /// 全局变量，装载语句执行后返回信息
        /// </summary>
        public string Message;
        #region 2012-10-04更新脚本
        public List<string> ExecuteSQL2012100401(bool IsExcute, string constr)
        {
            list = new List<string>();
            if (IsExcute)
            {
                string SQL = @"if not exists (select * from TbStaff WHERE StaffLoginName='admin')
INSERT INTO dbo.TbStaff(StaffLoginName,StaffPwd,StaffName,IsDeparture)
VALUES ('admin','111111','admin','0')";
                Message = SqlHelper.ExcuteQueryReturnMessage(constr, SQL, CommandType.Text);
            }
            list.Add("插入默认的系统角色:" + Message);
            return list;
        }

        public List<string> ExecuteSQL2012100402(bool IsExcute, string constr)
        {
            list = new List<string>();
            if (IsExcute)
            {
                string SQL = @"if not  exists (select A.name as DefaultName,B.name as TableName,* from sysobjects A
inner join sysobjects B on A.parent_obj = B.id
INNER join syscolumns C on B.id=object_id('TbRole') AND  C.name='RoleCreatTime'
where A.xtype = 'D' and B.xtype = 'U' and B.name = 'TbRole')
ALTER table TbRole
ADD DEFAULT (getdate()) FOR RoleCreatTime";
                Message = SqlHelper.ExcuteQueryReturnMessage(constr, SQL, CommandType.Text);
            }
            list.Add("添加默认值到角色表的创建时间列:" + Message);
            return list;
        }


        public List<string> ExecuteSQL2012100403(bool IsExcute, string constr)
        {
            list = new List<string>();
            if (IsExcute)
            {
                string SQL = @"if not  exists (select A.name as DefaultName,B.name as TableName,* from sysobjects A
inner join sysobjects B on A.parent_obj = B.id
INNER join syscolumns C on B.id=object_id('TbCompetence') AND  C.name='CreateTime'
where A.xtype = 'D' and B.xtype = 'U' and B.name = 'TbCompetence')
ALTER table TbCompetence
ADD DEFAULT (getdate()) FOR CreateTime";
                Message = SqlHelper.ExcuteQueryReturnMessage(constr, SQL, CommandType.Text);
            }
            list.Add("添加默认值到权限表的创建时间列:" + Message);
            return list;
        }

        public List<string> ExecuteSQL2012100404(bool IsExcute, string constr)
        {
            list = new List<string>();
            if (IsExcute)
            {
                string SQL = @"if not exists (select * from TbCompetence WHERE CompetenceCode='SysCustManger')
INSERT INTO dbo.TbCompetence(CompetenceName,CompetenceCode,CreateTime,Remark,ParentID,Url)
VALUES ('客户管理系统','SysCustManger',getdate(),'',0,'')
";
                Message = SqlHelper.ExcuteQueryReturnMessage(constr, SQL, CommandType.Text);
            }
            list.Add("添加客户关系关系系统菜单项:" + Message);
            return list;
        }

        public List<string> ExecuteSQL2012100405(bool IsExcute, string constr)
        {
            list = new List<string>();
            if (IsExcute)
            {
                string SQL = @"declare @CompetenceID decimal 
select @CompetenceID=CompetenceID from TbCompetence where CompetenceCode='SysCustManger'
if not exists (select * from TbCompetence WHERE CompetenceCode='CompanyManger')
INSERT INTO dbo.TbCompetence(CompetenceName,CompetenceCode,CreateTime,Remark,ParentID,Url)
VALUES ('公司管理','CompanyManger',getdate() ,'',@CompetenceID,'~/Module/CompanyManger/CompanyManger.aspx')

";
                Message = SqlHelper.ExcuteQueryReturnMessage(constr, SQL, CommandType.Text);
            }
            list.Add("添加公司管理菜单项:" + Message);
            return list;
        }

        public List<string> ExecuteSQL2012100406(bool IsExcute, string constr)
        {
            list = new List<string>();
            if (IsExcute)
            {
                string SQL = @"declare @CompetenceID decimal 
select @CompetenceID=CompetenceID from TbCompetence where CompetenceCode='SysCustManger'
if not exists (select * from TbCompetence WHERE CompetenceCode='DepartmentManger')
INSERT INTO dbo.TbCompetence(CompetenceName,CompetenceCode,CreateTime,Remark,ParentID,Url)
VALUES ('部门管理','DepartmentManger',getdate() ,'',@CompetenceID,'~/Module/DepartmentManger/DepartmentManger.aspx')";
                Message = SqlHelper.ExcuteQueryReturnMessage(constr, SQL, CommandType.Text);
            }
            list.Add("添加部门管理菜单项:" + Message);
            return list;
        }


        public List<string> ExecuteSQL2012100407(bool IsExcute, string constr)
        {
            list = new List<string>();
            if (IsExcute)
            {
                string SQL = @"declare @CompetenceID decimal 
select @CompetenceID=CompetenceID from TbCompetence where CompetenceCode='SysCustManger'
if not exists (select * from TbCompetence WHERE CompetenceCode='CustServerManger')
INSERT INTO dbo.TbCompetence(CompetenceName,CompetenceCode,CreateTime,Remark,ParentID,Url)
VALUES ('客户服务管理','CustServerManger',getdate() ,'',@CompetenceID,'~/Module/CustServerManger/CustServerManger.aspx')";
                Message = SqlHelper.ExcuteQueryReturnMessage(constr, SQL, CommandType.Text);
            }
            list.Add("添加客户服务管理菜单项:" + Message);
            return list;
        }


        public List<string> ExecuteSQL2012100408(bool IsExcute, string constr)
        {
            list = new List<string>();
            if (IsExcute)
            {
                string SQL = @"declare @CompetenceID decimal 
select @CompetenceID=CompetenceID from TbCompetence where CompetenceCode='SysCustManger'
if not exists (select * from TbCompetence WHERE CompetenceCode='MyServerCust')
INSERT INTO dbo.TbCompetence(CompetenceName,CompetenceCode,CreateTime,Remark,ParentID,Url)
VALUES ('我的服务客户管理','MyServerCust',getdate() ,'',@CompetenceID,'~/Module/CustServerManger/MyServerCustList.aspx')";
                Message = SqlHelper.ExcuteQueryReturnMessage(constr, SQL, CommandType.Text);
            }
            list.Add("添加我的服务客户菜单项:" + Message);
            return list;
        }

        public List<string> ExecuteSQL2012100409(bool IsExcute, string constr)
        {
            list = new List<string>();
            if (IsExcute)
            {
                string SQL = @"declare @CompetenceID decimal 
select @CompetenceID=CompetenceID from TbCompetence where CompetenceCode='SysCustManger'
if not exists (select * from TbCompetence WHERE CompetenceCode='CustManger')
INSERT INTO dbo.TbCompetence(CompetenceName,CompetenceCode,CreateTime,Remark,ParentID,Url)
VALUES ('客户管理','CustManger',getdate() ,'',@CompetenceID,'')";
                Message = SqlHelper.ExcuteQueryReturnMessage(constr, SQL, CommandType.Text);
            }
            list.Add("添加客户管理菜单项:" + Message);
            return list;
        }

        public List<string> ExecuteSQL2012100410(bool IsExcute, string constr)
        {
            list = new List<string>();
            if (IsExcute)
            {
                string SQL = @"
declare @CompetenceID decimal 
select @CompetenceID=CompetenceID from TbCompetence where CompetenceCode='CustManger'
if not exists (select * from TbCompetence WHERE CompetenceCode='IntentCust')
INSERT INTO dbo.TbCompetence(CompetenceName,CompetenceCode,CreateTime,Remark,ParentID,Url)
VALUES ('意向客户','IntentCust',getdate() ,'',@CompetenceID,'~/Module/CustManger/CustManger.aspx?CustType=IntentCust')
";
                Message = SqlHelper.ExcuteQueryReturnMessage(constr, SQL, CommandType.Text);
            }
            list.Add("添加意向客户菜单项:" + Message);
            return list;
        }

        public List<string> ExecuteSQL2012100411(bool IsExcute, string constr)
        {
            list = new List<string>();
            if (IsExcute)
            {
                string SQL = @"declare @CompetenceID decimal 
select @CompetenceID=CompetenceID from TbCompetence where CompetenceCode='CustManger'
if not exists (select * from TbCompetence WHERE CompetenceCode='ProtectCust')
INSERT INTO dbo.TbCompetence(CompetenceName,CompetenceCode,CreateTime,Remark,ParentID,Url)
VALUES ('保护客户','ProtectCust',getdate() ,'',@CompetenceID,'~/Module/CustManger/CustManger.aspx?CustType=ProtectCust')";
                Message = SqlHelper.ExcuteQueryReturnMessage(constr, SQL, CommandType.Text);
            }
            list.Add("添加保护客户菜单项:" + Message);
            return list;
        }


        public List<string> ExecuteSQL2012100412(bool IsExcute, string constr)
        {
            list = new List<string>();
            if (IsExcute)
            {
                string SQL = @"
if not exists (select * from TbCompetence WHERE CompetenceCode='SysSetManger')
INSERT INTO dbo.TbCompetence(CompetenceName,CompetenceCode,CreateTime,Remark,ParentID,Url)
VALUES ('系统设置','SysSetManger',getdate() ,'',0,'')";
                Message = SqlHelper.ExcuteQueryReturnMessage(constr, SQL, CommandType.Text);
            }
            list.Add("添加系统设置菜单项:" + Message);
            return list;
        }

        public List<string> ExecuteSQL2012100413(bool IsExcute, string constr)
        {
            list = new List<string>();
            if (IsExcute)
            {
                string SQL = @"declare @CompetenceID decimal 
select @CompetenceID=CompetenceID from TbCompetence where CompetenceCode='SysSetManger'
if not exists (select * from TbCompetence WHERE CompetenceCode='StaffManger')
INSERT INTO dbo.TbCompetence(CompetenceName,CompetenceCode,CreateTime,Remark,ParentID,Url)
VALUES ('员工信息管理','StaffManger',getdate() ,'',@CompetenceID,'~/Module/StaffManger/StaffIndex.aspx')";
                Message = SqlHelper.ExcuteQueryReturnMessage(constr, SQL, CommandType.Text);
            }
            list.Add("添加员工管理菜单项:" + Message);
            return list;
        }

        public List<string> ExecuteSQL2012100414(bool IsExcute, string constr)
        {
            list = new List<string>();
            if (IsExcute)
            {
                string SQL = @"declare @CompetenceID decimal 
select @CompetenceID=CompetenceID from TbCompetence where CompetenceCode='SysSetManger'
if not exists (select * from TbCompetence WHERE CompetenceCode='RoleManger')
INSERT INTO dbo.TbCompetence(CompetenceName,CompetenceCode,CreateTime,Remark,ParentID,Url)
VALUES ('部门信息管理','DepartmentManger',getdate() ,'',@CompetenceID,'~/Module/DepartmentManger/DepartmentIndex.aspx')
";
                Message = SqlHelper.ExcuteQueryReturnMessage(constr, SQL, CommandType.Text);
            }
            list.Add("添加部门管理菜单项:" + Message);
            return list;
        }


        public List<string> ExecuteSQL2012100415(bool IsExcute, string constr)
        {
            list = new List<string>();
            if (IsExcute)
            {
                string SQL = @"declare @CompetenceID decimal 
select @CompetenceID=CompetenceID from TbCompetence where CompetenceCode='SysSetManger'
if not exists (select * from TbCompetence WHERE CompetenceCode='RoleManger')
INSERT INTO dbo.TbCompetence(CompetenceName,CompetenceCode,CreateTime,Remark,ParentID,Url)
VALUES ('角色信息管理','RoleManger',getdate() ,'',@CompetenceID,'~/Module/RoleManger/RoleManger.aspx')";

                Message = SqlHelper.ExcuteQueryReturnMessage(constr, SQL, CommandType.Text);
            }
            list.Add("添加部门管理菜单项:" + Message);
            return list;
        }
        #endregion


        public List<string> ExecuteSQL2012100501(bool IsExcute, string constr)
        {
            list = new List<string>();
            if (IsExcute)
            {
                string SQL = @"if not exists (select 1 from syscolumns where id=object_id('TbDepartment') and name='CreateTime')
alter table TbDepartment
add CreateTime datetime default(getdate())";
                Message = SqlHelper.ExcuteQueryReturnMessage(constr, SQL, CommandType.Text);
            }
            list.Add("部门表添加创建时间列默认当前时间:" + Message);
            return list;
        }

        public List<string> ExecuteSQL2012100502(bool IsExcute, string constr)
        {
            list = new List<string>();
            if (IsExcute)
            {
                string SQL = @"if not exists (select 1 from syscolumns
 where id=object_id('TbStaffInfo') and name='Hometown')
alter table TbStaffInfo
add Hometown varchar(20)";
                Message = SqlHelper.ExcuteQueryReturnMessage(constr, SQL, CommandType.Text);
            }
            list.Add("员工信息表添加籍贯:" + Message);
            return list;
        }


        public List<string> ExecuteSQL2012100503(bool IsExcute, string constr)
        {
            list = new List<string>();
            if (IsExcute)
            {
                string SQL = @"if not exists (select 1 from syscolumns
 where id=object_id('TbStaffInfo') and name='Marital')
alter table TbStaffInfo
add Marital bit";
                Message = SqlHelper.ExcuteQueryReturnMessage(constr, SQL, CommandType.Text);
            }
            list.Add("员工信息表添加婚姻状况列:" + Message);
            return list;
        }

        public List<string> ExecuteSQL2012100504(bool IsExcute, string constr)
        {
            list = new List<string>();
            if (IsExcute)
            {
                string SQL = @"
if not exists (select 1 from syscolumns
 where id=object_id('TbStaffInfo') and name='HomeAddress')
alter table TbStaffInfo
add HomeAddress varchar(200)";
                Message = SqlHelper.ExcuteQueryReturnMessage(constr, SQL, CommandType.Text);
            }
            list.Add("员工信息表添加家庭地址列:" + Message);
            return list;
        }


        public List<string> ExecuteSQL2012100505(bool IsExcute, string constr)
        {
            list = new List<string>();
            if (IsExcute)
            {
                string SQL = @"if not exists (select 1 from syscolumns
 where id=object_id('TbStaffInfo') and name='QQ')
alter table TbStaffInfo
add QQ varchar(20)";
                Message = SqlHelper.ExcuteQueryReturnMessage(constr, SQL, CommandType.Text);
            }
            list.Add("员工信息表添加QQ列:" + Message);
            return list;
        }

        public List<string> ExecuteSQL2012100506(bool IsExcute, string constr)
        {
            list = new List<string>();
            if (IsExcute)
            {
                string SQL = @"if not exists (select 1 from syscolumns
 where id=object_id('TbStaffInfo') and name='mail')
alter table TbStaffInfo
add mail varchar(50)";
                Message = SqlHelper.ExcuteQueryReturnMessage(constr, SQL, CommandType.Text);
            }
            list.Add("员工信息表添加邮箱列:" + Message);
            return list;
        }


        public List<string> ExecuteSQL2012100507(bool IsExcute, string constr)
        {
            list = new List<string>();
            if (IsExcute)
            {
                string SQL = @"if  exists (select 1 from syscolumns where id=object_id('TbStaffInfo') and name='Marital')
alter table dbo.TbStaffInfo
alter column Marital varchar(20)";
                Message = SqlHelper.ExcuteQueryReturnMessage(constr, SQL, CommandType.Text);
            }
            list.Add("员工信息表添加邮箱列:" + Message);
            return list;
        }

        public List<string> ExecuteSQL2012100601(bool IsExcute, string constr)
        {
            list = new List<string>();
            if (IsExcute)
            {
                string SQL = @"if not exists (select 1 from syscolumns where id=object_id('tbrole') and name='ProtectCount')
alter table tbrole
add ProtectCount int";
                Message = SqlHelper.ExcuteQueryReturnMessage(constr, SQL, CommandType.Text);
            }
            list.Add("角色信息表添加保护客户人数列:" + Message);
            return list;
        }

        public List<string> ExecuteSQL2012100602(bool IsExcute, string constr)
        {
            list = new List<string>();
            if (IsExcute)
            {
                string SQL = @"if not exists (select 1 from syscolumns where id=object_id('TbCust') and name='Legal')
alter table TbCust
add Legal varchar(30)";
                Message = SqlHelper.ExcuteQueryReturnMessage(constr, SQL, CommandType.Text);
            }
            list.Add("客户信息表添加法人代表列:" + Message);
            return list;
        }


        public List<string> ExecuteSQL2012100603(bool IsExcute, string constr)
        {
            list = new List<string>();
            if (IsExcute)
            {
                string SQL = @"if not exists (select 1 from syscolumns
 where id=object_id('tbcust') and name='IsDelet')
ALTER table tbcust
add IsDelet bit default(0)";
                Message = SqlHelper.ExcuteQueryReturnMessage(constr, SQL, CommandType.Text);
            }
            list.Add("客户信息表添加是否删除列:" + Message);
            return list;
        }


        public List<string> ExecuteSQL2012100804(bool IsExcute, string constr)
        {
            list = new List<string>();
            if (IsExcute)
            {
                string SQL = @"if not exists (select 1 from syscolumns where id=object_id('TbDepartment') and name='IsServer')
alter table TbDepartment
add IsServer bit default(0)";
                Message = SqlHelper.ExcuteQueryReturnMessage(constr, SQL, CommandType.Text);
            }
            list.Add("部门信息表添加是否服务列:" + Message);
            return list;
        }




        public List<string> ExecuteSQL2012101101(bool IsExcute, string constr)
        {
            list = new List<string>();
            if (IsExcute)
            {
                string SQL = @"declare @CompetenceID decimal 
select @CompetenceID=CompetenceID from TbCompetence where CompetenceCode='SysSetManger'
if not exists (select * from TbCompetence WHERE CompetenceCode='NoteManger')
INSERT INTO dbo.TbCompetence(CompetenceName,CompetenceCode,CreateTime,Remark,ParentID,Url)
VALUES ('公告信息管理','NoteManger',getdate() ,'',@CompetenceID,'~/Module/NoteManger/NoteManger.aspx')";

                Message = SqlHelper.ExcuteQueryReturnMessage(constr, SQL, CommandType.Text);
            }
            list.Add("添加公告管理菜单项:" + Message);
            return list;
        }


        public List<string> ExecuteSQL2012101102(bool IsExcute, string constr)
        {
            list = new List<string>();
            if (IsExcute)
            {
                string SQL = @"if not exists (select 1
            from  sysobjects
           where  id = object_id('TbNote')
            and   type = 'U')
create table TbNote (
   NoteID               int                  identity,
   Summary              varchar(300)         null,
   CreateTime           datetime             null  default(getdate()) ,
   CreateName           varchar(50)          null,
   FilePath             varchar(100)         null,
   [state]				bit					 default(1)
   constraint PK_TABLE_13 primary key (NoteID)
)
";

                Message = SqlHelper.ExcuteQueryReturnMessage(constr, SQL, CommandType.Text);
            }
            list.Add("添加公告管理表:" + Message);
            return list;
        }




        public List<string> ExecuteSQL2012101501(bool IsExcute, string constr)
        {
            list = new List<string>();
            if (IsExcute)
            {
                string SQL = @"declare @CompetenceID decimal 
select @CompetenceID=CompetenceID from TbCompetence where CompetenceCode='SysSetManger'
if not exists (select * from TbCompetence WHERE CompetenceCode='StaffPersonalInfo')
INSERT INTO dbo.TbCompetence(CompetenceName,CompetenceCode,CreateTime,Remark,ParentID,Url)
VALUES ('员工个人信息管理','StaffPersonalInfo',getdate() ,'',@CompetenceID,'~/Module/StaffManger/StaffPersonalInfo.aspx')";

                Message = SqlHelper.ExcuteQueryReturnMessage(constr, SQL, CommandType.Text);
            }
            list.Add("添加员工个人信息管理单项:" + Message);
            return list;
        }



        public List<string> ExecuteSQL2012101502(bool IsExcute, string constr)
        {
            list = new List<string>();
            if (IsExcute)
            {
                string SQL = @"if not exists (select 1 from syscolumns where id=object_id('tbnote') and name='Content')
alter table tbnote
add Content text";

                Message = SqlHelper.ExcuteQueryReturnMessage(constr, SQL, CommandType.Text);
            }
            list.Add("公告管理表添加公告内容字段:" + Message);
            return list;
        }


        public List<string> ExecuteSQL2012101503(bool IsExcute, string constr)
        {
            list = new List<string>();
            if (IsExcute)
            {
                string SQL = @"declare @CompetenceID decimal 
select @CompetenceID=CompetenceID from TbCompetence where CompetenceCode='SysCustManger'
Update TbCompetence
SET ParentID=@CompetenceID
WHERE CompetenceCode='StaffPersonalInfo'";

                Message = SqlHelper.ExcuteQueryReturnMessage(constr, SQL, CommandType.Text);
            }
            list.Add("改变个人信息管理父节点:" + Message);
            return list;
        }



        public List<string> ExecuteSQL2012101504(bool IsExcute, string constr)
        {
            list = new List<string>();
            if (IsExcute)
            {
                string SQL = @"declare @CompetenceID decimal 
select @CompetenceID=CompetenceID from TbCompetence where CompetenceCode='CustManger'
if not exists (select * from TbCompetence WHERE CompetenceCode='SigningCust')
INSERT INTO dbo.TbCompetence(CompetenceName,CompetenceCode,CreateTime,Remark,ParentID,Url)
VALUES ('已签客户','SigningCust',getdate() ,'',@CompetenceID,'~/Module/CustManger/CustManger.aspx?CustType=SigningCust')";

                Message = SqlHelper.ExcuteQueryReturnMessage(constr, SQL, CommandType.Text);
            }
            list.Add("添加已签客户菜单项:" + Message);
            return list;
        }



        public List<string> ExecuteSQL2012101801(bool IsExcute, string constr)
        {
            list = new List<string>();
            if (IsExcute)
            {
                string SQL = @"if not exists (select 1 from syscolumns where id=object_id('tbcust') and name='TelPhone')
ALTER table tbcust
alter column TelPhone varchar(100)

if not exists (select 1 from syscolumns where id=object_id('tbcust') and name='CustName')
ALTER table tbcust
alter column CustName varchar(150)

if not exists (select 1 from syscolumns where id=object_id('tbcust') and name='QQ')
ALTER table tbcust
alter column QQ varchar(150)

if not exists (select 1 from syscolumns where id=object_id('tbcust') and name='Linkman')
ALTER table tbcust
alter column Linkman varchar(500)

if not exists (select 1 from syscolumns where id=object_id('tbcust') and name='CompanyName')
ALTER table tbcust
alter column CompanyName varchar(500)";

                Message = SqlHelper.ExcuteQueryReturnMessage(constr, SQL, CommandType.Text);
            }
            list.Add("修改客户相关字段长度:" + Message);
            return list;
        }



        public List<string> ExecuteSQL2012102101(bool IsExcute, string constr)
        {
            list = new List<string>();
            if (IsExcute)
            {
                string SQL = @"if not exists (select 1 from syscolumns where id=object_id('tbcust') and name='Opinion')
alter table tbcust
add Opinion text";
                Message = SqlHelper.ExcuteQueryReturnMessage(constr, SQL, CommandType.Text);
            }
            list.Add("添加经理意见字段:" + Message);
            return list;
        }


        public List<string> ExecuteSQL2012102201(bool IsExcute, string constr)
        {
            list = new List<string>();
            if (IsExcute)
            {
                string SQL = @"if  exists (select 1 from syscolumns where id=object_id('tbcust') and name='TelPhone')
ALTER table tbcust
alter column TelPhone varchar(100)

if  exists (select 1 from syscolumns where id=object_id('tbcust') and name='CustName')
ALTER table tbcust
alter column CustName varchar(150)

if  exists (select 1 from syscolumns where id=object_id('tbcust') and name='QQ')
ALTER table tbcust
alter column QQ varchar(150)

if  exists (select 1 from syscolumns where id=object_id('tbcust') and name='Linkman')
ALTER table tbcust
alter column Linkman varchar(500)

if  exists (select 1 from syscolumns where id=object_id('tbcust') and name='CompanyName')
ALTER table tbcust
alter column CompanyName varchar(500)";

                Message = SqlHelper.ExcuteQueryReturnMessage(constr, SQL, CommandType.Text);
            }
            list.Add("修改客户相关字段长度:" + Message);
            return list;
        }

        public List<string> ExecuteSQL2012102202(bool IsExcute, string constr)
        {
            list = new List<string>();
            if (IsExcute)
            {
                string SQL = @"if not exists (select 1 from syscolumns where id=object_id('TbCustFollow') and name='intentions')
ALTER table TbCustFollow
add intentions varchar(100)";
                Message = SqlHelper.ExcuteQueryReturnMessage(constr, SQL, CommandType.Text);
            }
            list.Add("添加客户意向:" + Message);
            return list;
        }

        public List<string> ExecuteSQL2012102203(bool IsExcute, string constr)
        {
            list = new List<string>();
            if (IsExcute)
            {
                string SQL = @"if not exists (select 1 from syscolumns where id=object_id('TbCustFollow') and name='Remark')
ALTER table TbCustFollow
add Remark varchar(1000)";
                Message = SqlHelper.ExcuteQueryReturnMessage(constr, SQL, CommandType.Text);
            }
            list.Add("添加备份:" + Message);
            return list;
        }

        public List<string> ExecuteSQL2012102204(bool IsExcute, string constr)
        {
            list = new List<string>();
            if (IsExcute)
            {
                string SQL = @"if not exists (select 1 from syscolumns where id=object_id('TbCustFollow') and name='NextFlow')
ALTER table TbCustFollow
add NextFlow datetime";
                Message = SqlHelper.ExcuteQueryReturnMessage(constr, SQL, CommandType.Text);
            }
            list.Add("添加下次跟进时间:" + Message);
            return list;
        }

        public List<string> ExecuteSQL2012111501(bool IsExcute, string constr)
        {
            list = new List<string>();
            if (IsExcute)
            {
                string SQL = @"if exists (select 1 from syscolumns where id=object_id('TbCustFollow') and name='FollowType')
ALTER table dbo.TbCustFollow
alter column FollowType varchar(50)";
                Message = SqlHelper.ExcuteQueryReturnMessage(constr, SQL, CommandType.Text);
            }
            list.Add("修改跟进类型字段长度:" + Message);
            return list;
        }

        public List<string> ExecuteSQL2012112301(bool IsExcute, string constr)
        {
            list = new List<string>();
            if (IsExcute)
            {
                string SQL = @"if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_PageView]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
-- 删除存储过程
drop procedure [dbo].[sp_PageView]
GO
--TOP n 实现的通用分页存储过程

CREATE PROC [dbo].[sp_PageView]

@tbname     sysname,               --要分页显示的表名

@FieldKey   nvarchar(1000),      --用于定位记录的主键(惟一键)字段,可以是逗号分隔的多个字段

@PageCurrent int=1,               --要显示的页码

@PageSize   int=10,                --每页的大小(记录数)

@FieldShow nvarchar(1000)='',      --以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段

@FieldOrder nvarchar(1000)='',      --以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC 用于指定排序顺序

                                          

@Where    nvarchar(1000)='',     --查询条件

@PageCount int OUTPUT             --总页数

AS

SET NOCOUNT ON

--检查对象是否有效

IF OBJECT_ID(@tbname) IS NULL

BEGIN

    RAISERROR(N'对象不存在',1,16,@tbname)

    RETURN

END

IF OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTable')=0

    AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsView')=0

    AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTableFunction')=0

BEGIN

    RAISERROR(N'不是表、视图或者表值函数',1,16,@tbname)

    RETURN

END

 

--分页字段检查

IF ISNULL(@FieldKey,N'')=''

BEGIN

    RAISERROR(N'分页处理需要主键（或者惟一键）',1,16)

    RETURN

END

 

--其他参数检查及规范

IF ISNULL(@PageCurrent,0)<1 SET @PageCurrent=1

IF ISNULL(@PageSize,0)<1 SET @PageSize=10

IF ISNULL(@FieldShow,N'')=N'' SET @FieldShow=N'*'

IF ISNULL(@FieldOrder,N'')=N''

    SET @FieldOrder=N''

ELSE

    SET @FieldOrder=N'ORDER BY '+LTRIM(@FieldOrder)

IF ISNULL(@Where,N'')=N''

    SET @Where=N''

ELSE

    SET @Where=N'WHERE ('+@Where+N')'

 

--如果@PageCount为NULL值,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算总页数,对于不想计算总页数的处理而言,可以给@PageCount赋值)

IF @PageCount IS NULL

BEGIN

    DECLARE @sql nvarchar(4000)

    SET @sql=N'SELECT COUNT(*)'

        +N' FROM '+@tbname

        +N' '+@Where

		exec(@sql)
    --EXEC sp_executesql @sql,N'@PageCount int OUTPUT',@PageCount OUTPUT

    --SET @PageCount=(@PageCount+@PageSize-1)/@PageSize

END

 

--计算分页显示的TOPN值

DECLARE @TopN varchar(20),@TopN1 varchar(20)

SELECT @TopN=@PageSize,

    @TopN1=(@PageCurrent-1)*@PageSize

 

--第一页直接显示

IF @PageCurrent=1

    EXEC(N'SELECT TOP '+@TopN

        +N' '+@FieldShow

        +N' FROM '+@tbname

        +N' '+@Where

        +N' '+@FieldOrder)

ELSE

BEGIN

    --处理别名

    IF @FieldShow=N'*'

        SET @FieldShow=N'a.*'

 

    --生成主键(惟一键)处理条件

    DECLARE @Where1 nvarchar(4000),@Where2 nvarchar(4000),

        @s nvarchar(1000),@Field sysname

    SELECT @Where1=N'',@Where2=N'',@s=@FieldKey

    WHILE CHARINDEX(N',',@s)>0

        SELECT @Field=LEFT(@s,CHARINDEX(N',',@s)-1),

            @s=STUFF(@s,1,CHARINDEX(N',',@s),N''),

            @Where1=@Where1+N' AND a.'+@Field+N'=b.'+@Field,

            @Where2=@Where2+N' AND b.'+@Field+N' IS NULL',

            @Where=REPLACE(@Where,@Field,N'a.'+@Field),

            @FieldOrder=REPLACE(@FieldOrder,@Field,N'a.'+@Field),

            @FieldShow=REPLACE(@FieldShow,@Field,N'a.'+@Field)

    SELECT @Where=REPLACE(@Where,@s,N'a.'+@s),

        @FieldOrder=REPLACE(@FieldOrder,@s,N'a.'+@s),

        @FieldShow=REPLACE(@FieldShow,@s,N'a.'+@s),

        @Where1=STUFF(@Where1+N' AND a.'+@s+N'=b.'+@s,1,5,N''),   

        @Where2=CASE

            WHEN @Where='' THEN N'WHERE ('

            ELSE @Where+N' AND ('

            END+N'b.'+@s+N' IS NULL'+@Where2+N')'

 

    --执行查询

    EXEC(N'SELECT TOP '+@TopN

        +N' '+@FieldShow

        +N' FROM '+@tbname

        +N' a LEFT JOIN(SELECT TOP '+@TopN1

        +N' '+@FieldKey

        +N' FROM '+@tbname

        +N' a '+@Where

        +N' '+@FieldOrder

        +N')b ON '+@Where1

        +N' '+@Where2

        +N' '+@FieldOrder) 

END";
                Message = SqlHelper.ExcuteQueryReturnMessage(constr, SQL, CommandType.Text);
            }
            list.Add("增加分页存储过程:" + Message);
            return list;
        }

    }
}
